package org.example.Dao;

import org.example.entity.Topic;
import org.example.entity.VoteChar;
import org.example.utils.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class VoteDao {

    public int save(int oid, int pid, int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "insert into vote (oid, pid, tid) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, oid);
        preparedStatement.setInt(2, pid);
        preparedStatement.setInt(3, tid);
        int count = preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
        return count;
    }

    public int getVoteCount(int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select count(*) from vote where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, tid);
        ResultSet resultSet = preparedStatement.executeQuery();
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        preparedStatement.close();
        connection.close();
        return count;
    }

    public int isVoted(int pid, int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select count(*) from vote where pid = ? and tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, pid);
        preparedStatement.setInt(2, tid);
        ResultSet resultSet = preparedStatement.executeQuery();
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        preparedStatement.close();
        connection.close();
        return count;
    }

    public int votedOptionId(int pid, int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select oid from vote where pid = ? and tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, pid);
        preparedStatement.setInt(2, tid);
        ResultSet resultSet = preparedStatement.executeQuery();
        int oid = 0;
        if (resultSet.next()) {
            oid = resultSet.getInt("oid");
        }
        preparedStatement.close();
        connection.close();
        return oid;
    }

    public List<VoteChar> getCharInfo(int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "SELECT o.oid        AS oid, o.name   AS name,COUNT(v.oid) AS count " +
                "FROM `option` o " +
                "LEFT JOIN vote v ON o.oid = v.oid AND v.tid = ? WHERE o.tid = ? " +
                "GROUP BY o.oid, o.name ORDER BY count DESC;";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, tid);
        preparedStatement.setInt(2, tid);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<VoteChar> voteChars = new ArrayList<>();
        while (resultSet.next()) {
            voteChars.add(new VoteChar(resultSet.getInt("oid"), resultSet.getString("name"), resultSet.getInt("count")));
        }
        preparedStatement.close();
        connection.close();

        return voteChars;
    }

    public List<Topic> getMyVoted(int pid) throws Exception {
        Connection connection = DruidUtil.getConnection();
//        String sql = "SELECT t.* FROM topic t JOIN vote v ON t.tid = v.tid WHERE v.pid = ?";
        String sql = "SELECT t.* FROM topic t JOIN vote v ON t.tid = v.tid and t.status != 3 WHERE v.pid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, pid);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Topic> topics = new ArrayList<>();
        while (resultSet.next()) {
            Topic topic = new Topic();
            topic.setTid(resultSet.getInt("tid"));
            topic.setPid(resultSet.getInt("pid"));
            topic.setTitle(resultSet.getString("title"));
            topic.setDesc(resultSet.getString("desc"));
            topic.setAuthor(resultSet.getString("author"));
            topic.setCreateDate(resultSet.getDate("createDate"));
            topic.setBeginDate(resultSet.getDate("beginDate"));
            topic.setEndDate(resultSet.getDate("endDate"));
            topic.setStatus(resultSet.getInt("status"));
            topic.setUrl(resultSet.getString("url"));
            topics.add(topic);
        }
        preparedStatement.close();
        connection.close();
        return topics;
    }
}
